<?php
App::uses('AppController', 'Controller');
/**
 * DataParams Controller
 *
 * @property DataParam $DataParam
 * @property PaginatorComponent $Paginator
 * @property SessionComponent $Session
 */

App::import('Vendor', 'PHPExcel/Classes/PHPExcel/IOFactory');
class DataParamsController extends AppController {

/**
 * Components
 *
 * @var array
 */
	public $components = array('Paginator', 'Session');

/**
 * index method
 *
 * @return void
 */
	public function admin_index() {
                if($this->request->is(array('post')))
                {
                    $data = $this->request->data['input-search'];
                    if(!empty($data))
                    {
                         $condition = array('OR'=>array('mr_code LIKE'=>"%$data%",'rr_no LIKE'=>"%$data%",'account_id'=>"$data"));
                        $this->Paginator->settings= array(
                            'conditions'=>$condition
                        );
                    }
                   
                            
                }
		$this->DataParam->recursive = 0;
		$this->set('dataPramas', $this->Paginator->paginate('DataParam'));
	}

/**
 * view method
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */
	public function view($id = null) {
		if (!$this->DataParam->exists($id)) {
			throw new NotFoundException(__('Invalid data prama'));
		}
		$options = array('conditions' => array('DataParam.' . $this->DataParam->primaryKey => $id));
		$this->set('dataPrama', $this->DataParam->find('first', $options));
	}

/**
 * add method
 *
 * @return void
 */
	public function add() {
		if ($this->request->is('post')) {
			$this->DataParam->create();
			if ($this->DataParam->save($this->request->data)) {
				$this->Session->setFlash(__('The data prama has been saved.'));
				return $this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The data prama could not be saved. Please, try again.'));
			}
		}
                                    $this->loadModel('DataParam');
                                    $dataPrama = $this->DataParam->find('list',array('fields'=>array('id','sdo')));
                                    $this->set(compact('dataPrama'));
	}

/**
 * edit method
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */
	public function admin_edit($id = null) {
		if (!$this->DataParam->exists($id)) {
			throw new NotFoundException(__('Invalid data prama'));
		}
		if ($this->request->is(array('post', 'put'))) {
			if ($this->DataParam->save($this->request->data)) {
				$this->Session->setFlash(__('The data prama has been saved.'));
				return $this->redirect(array('action' => 'admin_index'));
			} else {
				$this->Session->setFlash(__('The data prama could not be saved. Please, try again.'));
			}
		} else {
			$options = array('conditions' => array('DataParam.' . $this->DataParam->primaryKey => $id));
			$this->request->data = $this->DataParam->find('first', $options);
		}
                                 
	}

/**
 * delete method
 *
 * @throws NotFoundException
 * @param string $id
 * @return void
 */
	public function admin_delete($id = null) {
           	$this->DataParam->id = $id;
		if (!$this->DataParam->exists()) {
			throw new NotFoundException(__('Invalid data prama'));
		}
		if ($this->DataParam->delete()) {
			$this->Session->setFlash(__('The data prama has been deleted.'));
		} else {
			$this->Session->setFlash(__('The data prama could not be deleted. Please, try again.'));
		}
		return $this->redirect(array('action' => 'admin_index'));
	}
        /*
         * Import data
         */
        public function admin_import()
        {
                  set_time_limit(0);
           if ($_FILES['uploadfile']['error'] == 0) {
            $name = $_FILES['uploadfile']['name'];
            $size = $_FILES['uploadfile']['size'];
            preg_match('/\.[^\.]+$/i', $name, $ext);

            if ($ext[0] == ".xls" || $ext[0] == ".xlsx") {
                if ($size > 0) {
                    $tmp = $_FILES['uploadfile']['tmp_name'];
                    $objPHPExcel = PHPExcel_IOFactory::load($tmp);
                    $msgSuccesss = "";
                    $msgError = "";
                    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                        $worksheetTitle = $worksheet->getTitle();
                        $highestRow = $worksheet->getHighestRow(); 
                        $highestColumn = $worksheet->getHighestColumn();
                        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
                        $nrColumns = ord($highestColumn) - 64;
                        
                         for ($row = 5; $row <= $highestRow; ++$row) {
                             $val=array();
                            for ($col = 0; $col < $highestColumnIndex; ++$col) {
                                $cell = $worksheet->getCellByColumnAndRow($col, $row);
                                $val[] = $cell->getValue();
                                
                            }
                            
                            if(!empty($val) && count($val)>28)
                            {
                                $this->DataParam->set('id', "");
                                $this->DataParam->set("sdo", $val[1]);
                                $this->DataParam->set("tariff", $val[2]);
                                $this->DataParam->set("account_id", $val[3]);
                                $this->DataParam->set("rr_no", $val[4]);
                                $this->DataParam->set("regd_consumer_name", $val[5]);
                                $this->DataParam->set("address", $val[6]);
                              //  $this->DataParam->set("landmark", $val[6]);
                                $this->DataParam->set("area", $val[7]);
                                $this->DataParam->set("kw", $val[8]);
                                $this->DataParam->set("hp", $val[9]);
                                $this->DataParam->set("kva", $val[10]);
                                //$this->DataParam->set("total_load_in_kw", $val[11]);
                                $this->DataParam->set("constant", $val[11]);
                                $this->DataParam->set("deposits_held_", $val[12]);
                                $this->DataParam->set("date_of-server", $val[13]);
                                $this->DataParam->set("status", $val[14]);
                                 $this->DataParam->set("dtc_id", $val[15]);
                                $this->DataParam->set("dtc_name", $val[16]);
                                $this->DataParam->set("pole_id", $val[17]);
                                $this->DataParam->set("section", $val[18]);
                                $this->DataParam->set("mr_code", $val[19]);
                                $this->DataParam->set("credit_debit", $val[20]);
                                $this->DataParam->set("bbc", $val[20]);
                                $this->DataParam->set("asc", $val[20]);
                                $this->DataParam->set("other_debits", $val[20]);
                                $this->DataParam->set("mr_day", $val[21]);
                                $this->DataParam->set("spot_folio", $val[21]);
                              //  $this->DataParam->set("station_name", $val[15]);
                               // $this->DataParam->set("feeder_name", $val[16]);
                               
                                
                                
                                
                                

                                if ($this->DataParam->save()) {
                                    $msgSuccesss .= $row . ",";
                                } else {
                                    $msgError .= $row . ",";
                                }
                            } else {
                                $msgError .= $row . ",";
                            }
                        }
                        $msg = ($msgSuccesss !='')?rtrim($msgSuccesss,",") ." is inserted ":"";
                        $msg .=($msgError !='')?"- ".rtrim($msgError,",")." is not inserted ":"";
                        $this->Session->setFlash("Import data with number row: $msg");
                        
                    }
                } else {
                    $this->Session->setFlash("File empty");
                }
            } else {
                $this->Session->setFlash("Using  file .xls or xlsx");
            }
        } else {
            $this->Session->setFlash("File error");
        }
        return $this->redirect(array('action' => 'admin_index'));
    }
}
